<?php
/**
 *	Appublisher DB Class
 *
 *	Database Access Abstraction Object Base on PDO Driver: PHP 5.3.6+ required
 *	and MySQL 5.x+ required.
 *
 *	@package Appublisher
 *	@subpackage Database
 *	@author bluer
 *	@version 0.2
 */


class Apdb {
    /**
     * Amount of queries made
     *
     * @access private
     * @var int
     */
    protected $_num_queries = 0;

    /**
     * Saved queries that were executed
     *
     * @access private
     * @var array
     */
	protected $_queries;

    /**
     * Database Username
     *
     * @access private
     * @var string
     */
	protected $_dbUser;

    /**
     * Database Password
     *
     * @access private
     * @var string
     */
	protected $_dbPassword;

    /**
     * Database Name
     *
     * @access private
     * @var string
     */
	protected $_dbName;

    /**
     * Database PDO instance
     *
     * Saved persistent database connection
     *
     * @access private
     * @var PDO
     */
	protected static $_singleton = null;

    /**
     * Database Host
     *
     * @access protected
     * @var string
     */
    protected $_dbHost;

    /**
     * Database PDO instance
     *
     * @access protected
     * @var PDO
     */
    protected $_dbh;

    /**
     * A key=>value array of driver-specific connection options.
     *
     * @link http://us3.php.net/manual/en/pdo.constants.php
     * @link http://us3.php.net/manual/en/ref.pdo-mysql.php
     *
     * @access protected
     * @var array
     */
    protected $_options;

    /**
     * Statement object
     *
     * @access protected
     * @var object
     */
    protected $_stmt;

    /**
     * Using persistent connections or not.
     *
     * @access protected
     * @var bool
     */
    protected $_persistent;

    /**
     * MySQL result, which is either a resource or boolean.
     *
     * @access protected
     * @var mixed
     */
    protected $_result;

    /**
     * Database table columns charset
     *
     * @access public
     * @var string
     */
    public $charset;

    /**
     * Database table columns collate
     *
     * @access public
     * @var string
     */
    public $collate;

    /**
     * Table prefix
     *
     * @access public
     * @var string
     */
    public $prefix = '';

    /**
     * Whether to show SQL/DB errors.
     *
     * Default behavior is to show errors if DEBUG evaluated to true.
     *
     * @access private
     * @var bool
     */
    public $show_errors = false;


    /**
     * Connects to the database server and selects a database
     *
     * @param string $dbUser MySQL database user
     * @param string $dbPassword MySQL database password
     * @param string $dbName MySQL database name
     * @param string $dbHost MySQL database host
     * @param bool $persistent Using persistent connections or not. default: yes.
     * @param array $options A key=>value array of driver-specific connection options.
     */
    public function __construct($dbUser, $dbPassword, $dbName, $dbHost, $persistent = true, $options = array()) {
        if (!defined('PDO::MYSQL_ATTR_LOCAL_INFILE')) {
            $this->show_errors = true;
            $this->printError("PDO_MYSQL driver is not installed or enabled!");
        } elseif (version_compare(phpversion(), '5.3.6', '<=')) {
            // Make sure the server has the required PHP version
            $this->show_errors = true;
            $this->printError("PDO extension required PHP >= 5.3.6.");
        }

        if ( defined('DEBUG') && DEBUG )
            $this->show_errors = true;

        if (defined('TABLE_PREFIX')) $this->prefix = TABLE_PREFIX;

        $this->setCharset();
        $this->_dbName = $dbName;
        $this->_dbHost = $dbHost;
        $this->_dbUser = $dbUser;
        $this->_dbPassword = $dbPassword;
        $this->_options = $options;
        $this->_persistent = $persistent;

        if ($this->_persistent && self::$_singleton instanceof PDO) {
            $this->_dbh = self::$_singleton;
        } else {
            $this->connectDB();
        }
    }

    /**
     * PHP5 style destructor and will run when database object is destroyed.
     *
     * @return bool true
     */
    public function __destruct() {
        if ($this->show_errors) {
            $this->printDebug();
        }

        if (isset($this->_dbh)) {
            $this->_stmt = null;
            $this->_dbh = null;
        }

        return true;
    }

    /**
     * Set $this->charset and $this->collate
     *
     */
    protected function setCharset() {
        $this->charset = 'utf8';
        $this->collate = 'utf8_general_ci';

        if (defined('DB_CHARSET') && DB_CHARSET)
            $this->charset = DB_CHARSET;

        if (defined('DB_COLLATE') && DB_COLLATE )
            $this->collate = DB_COLLATE;
    }

    /**
     * Connect to and select database.
     *
     * @access private
     *
     * @return bool True with a successful connection, false on failure.
     */
     public function connectDB() {
        try {
            $dsn = sprintf('mysql:host=%s;dbname=%s;port=%s;charset=%s',
                $this->_dbHost,
                $this->_dbName,
                3306,
                $this->charset
            );

            if (empty($this->_options)) {
                $cmd = 'SET NAMES ' . $this->charset . ' COLLATE ' . $this->collate;
                $this->_options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::MYSQL_ATTR_INIT_COMMAND => $cmd,
                    //PDO::ATTR_TIMEOUT =>  3
                );
            }

            if ($this->_persistent) {
                self::$_singleton = new PDO($dsn, $this->_dbUser, $this->_dbPassword, $this->_options);
                self::$_singleton->setAttribute(PDO::ATTR_PERSISTENT, true);
                $this->_dbh = self::$_singleton;
            } else {
                $this->_dbh = new PDO($dsn, $this->_dbUser, $this->_dbPassword, $this->_options);
            }

            // Make sure the server has the required MySQL version
            if (version_compare($this->dbVersion(), '5.0', '<')) {
                $this->show_errors = true;
                $this->printError("PDO requires MySQL 5.x or higher");
                return false;
            }

        } catch(PDOException $e) {
            $this->printError($e);
            return false;
        }

        return true;
    }

    /**
     * Print SQL/DB error.
     *
     * @param mixed $err The error to display
     */
	protected function printError($err) {
        if ($this->show_errors) {
            if (is_object($err)) {
                printf("Error: Code(%s) - %s <br>", $err->getCode(), $err->getMessage());
            } elseif (is_string($err)) {
                printf("Error: $err <br>");
            }
        }
    }

    /**
     * Print Debug Info
     *
     * @param mixed $info The info to display
     */
    protected function printDebug($info = null) {
        if (is_string($info)) {
            printf("%s - INFO: %s<br><br>", date("Y-m-d H:i:s"), $info);
        } elseif (is_array($info)) {
            foreach($info as $key=>$value) {
                printf("%s - INFO: %s; %s<br><br>", $key, $value);
            }
        }

        if ($this->_queries) {
            printf("Amount of queries: %d<br><br>", $this->_num_queries);
            foreach($this->_queries as $single) {
                printf("%s - #SQL: %s; #Time: %s<br><br>", @date("Y-m-d H:i:s"), $single[0], $single[1]);
            }
        }
    }

    /**
     * Check that the connection to the database is still up. If not, try to reconnect.
     * If this function is unable to reconnect, it will forcibly die.
     *
     * @access private
     *
     * @return bool True if the connection is up.
     */
    protected function checkConnection() {
        if ($this->_dbh) return true;

        if (self::$_singleton) {
            $this->_dbh = self::$_singleton;
            return true;
        }

        // try to reconnect
        return $this->connectDB();
    }

    /**
     * Internal function to perform the query.
     *
     * @access private
     *
     * @param string $query The query to run
     * @param array $bind The parameters to bind to the SQL statement parameter
     */
    protected function _do_query($query, $bind = null) {
        if (!$this->checkConnection()) {
            $this->show_errors = true;
            $this->printError("Establishing a database connection failed.");
            return;
        }

        if (defined('SAVEQUERIES') && SAVEQUERIES) {
            $time_start = $this->timer_start();
        }

        try {
            if (!is_null($bind) && is_array($bind)) {
                $this->_stmt = $this->_dbh->prepare($query);
                $this->bind($bind);

                if ($this->_stmt->execute()) {
                    $this->_result = true;
                } else {
                    $this->_result = false;
                }
            } else {
                $this->_stmt = $this->_dbh->query($query);
                $this->_result = true;
            }
            $this->_num_queries++;

            if (defined('SAVEQUERIES') && SAVEQUERIES) {
                $this->_queries[] = array($query, $this->timer_stop($time_start));
            }

        } catch (PDOException $e) {
            $this->_result = false;
			$this->printError($e);
		}
	}
	
	/**
	 * Binds a parameter to the specified variable name
	 *
	 * @param array $bind The parameters to bind to the SQL statement parameter
	 */
	protected function bind($bind) {
		if (is_array($bind)) {
			foreach ($bind as $param=>$value) {
				switch (true) {
					case is_int($value):
						$type = PDO::PARAM_INT;
						break;
					
					case is_bool($value):
						$type = PDO::PARAM_BOOL;
						break;
						
					case is_null($value):
						$type = PDO::PARAM_NULL;
						break;
					
					default:
						$type = PDO::PARAM_STR;
				}
				
				$this->_stmt->bindValue($param, $value, $type);
			}
		}
	}
	
	/**
	 * Prepares a SQL query for safe execution. Uses sprintf()-like syntax.
	 *
	 * The following directives can be used in the query format string:
	 *   %d (integer)
	 *   %f (float)
	 *   %s (string)
	 *   %% (literal percentage sign - no argument needed)
	 *
	 * All of %d, %f, and %s are to be left unquoted in the query string and they need an argument passed for them.
	 * Literals (%) as parts of the query must be properly written as %%.
	 *
	 * This function only supports a small subset of the sprintf syntax; it only supports %d (integer), %f (float),
	 * and %s (string).
	 * Does not support sign, padding, alignment, width or precision specifiers.
	 * Does not support argument numbering/swapping.
	 *
	 * May be called like {@link http://php.net/sprintf sprintf()} or like {@link http://php.net/vsprintf vsprintf()}.
	 *
	 * Both %d and %s should be left unquoted in the query string.
	 *
	 *     Apdb::prepare("SELECT * FROM `table` WHERE `column` = %s AND `field` = %d", array('foo', 1337))
	 *
	 * @link http://php.net/sprintf Description of syntax.
	 *
	 * @param string $query Query statement with sprintf()-like placeholders
	 * @param array $args further variables to substitute into the query's placeholders if being called like
	 * 	{@link http://php.net/sprintf sprintf()}.
	 *
	 * @return null|false|string Sanitized query string, null if there is no query, false if there is an error and string
	 * 	if there was something to prepare
	 */
	public function prepare($query, $args) {
		if (is_null($query)) return null;

		$args = func_get_args();
		array_shift($args);
		// If args were passed as an array (as in vsprintf), move them up
		if (isset($args[0]) && is_array($args[0]))
			$args = $args[0];
		
		// in case someone mistakenly already singlequoted it
		$query = str_replace("'%s'", '%s', $query);
		
		// doublequote unquoting
		$query = str_replace('"%s"', '%s', $query);
		
		// Force floats to be locale unaware
		$query = preg_replace('|(?<!%)%f|' , '%F', $query);
		
		foreach ($args as &$arg) {
			if (!is_int($arg)) 
				$arg = $this->_dbh->quote($arg);
		}
		
		return @vsprintf($query, $args);
	}
	
	/**
	 * Perform a MySQL database query, using current database connection.
	 * Parameter bindind is recommended for security, or call Apdb::prepare()
	 * before call Apdb::query().
	 * 
	 * @param string $query Database query
	 * @param array $bind The parameters to bind to the SQL statement parameter
	 *
	 * @return int|false Number of rows affected/selected or false on error
	 */
	public function query($query, $bind = "") {
		$return_val = false;
		if ( is_null($query) || empty($query)) {
			return $return_val;
		}
		
		$this->_do_query($query, $bind);

		$pattern = '/^\s*(create|alter|truncate|drop|insert|delete|update|replace)\s/i';
		if (preg_match($pattern, $query) && $this->_result){  
			$return_val = $this->_stmt->rowCount();
		}
		
		return $return_val;
	}
	
	/**
	 * Returns the number of rows affected by the last SQL statement
	 *
	 * @return int
	 */
	public function rowCount() {
		if (!is_null($this->_stmt)) {
			return $this->_stmt->rowCount();
		}
		
		return 0;
	}
	
	/**
	 * Returns the ID of the last inserted row or sequence value from a
	 * sequence object.
	 *
	 * @return int
	 */
	public function lastInsertId() {
		if ($this->_dbh) {
			return $this->_dbh->lastInsertId();
		}
		
		return 0;
	}
	
	/**
	 * To begin a transaction
	 *
	 * @return bool TRUE or FALSE
	 */
	public function beginTransaction() {
		if ($this->_dbh) {
			return $this->_dbh->beginTransaction();
		}
		
		return false;
	}
	
	/**
	 * To end a transaction and commit yout changes
	 *
	 * @return bool TRUE or FALSE
	 */
	public function endTransaction() {
		if ($this->_dbh) {
			return $this->_dbh->commit();
		}
		
		return false;
	}
	
	/**
	 * To cancel a transaction and roll back your changes
	 *
	 * @return bool TRUE or FALSE
	 */
	public function cancelTransaction() {
		if ($this->_dbh) {
			return $this->_dbh->rollBack();
		}
		
		return false;	
	}
	
	/**
	 * List the tables in the current databases
	 *
	 * @return array tables' name
	 */
	public function showTables() {
		$tableList = array();
		
		$this->_do_query("SHOW TABLES");
		
		if ($this->_result) {
			while ($row = $this->_stmt->fetch(PDO::FETCH_NUM)) {
				$tableList[] = $row[0];
			}
		}
		
		return $tableList;
	}
	
	/**
	 * Retrieve an entire SQL result set from the database (i.e., many rows)
	 *
	 * Executes a SQL query and returns the entire SQL result.
	 *
	 * @param string $query SQL query.
	 * @param array $bind The parameters to bind to the SQL statement parameter
	 * @param string $output Optional. Specifies that the fetch method shall
	 * return each row as an object with variable names that correspond to the
	 * column names returned in the result set. Predefined Constants:
	 * PDO::FETCH_OBJ, PDO::FETCH_ASSOC, PDO::FETCH_NAMED, PDO::FETCH_NUM,
	 * PDO::FETCH_COLUMN, PDO::FETCH_CLASS
	 * 
	 * More: http://us3.php.net/manual/en/pdo.constants.php
	 * 
	 * @return mixed Database query results
	 */
	public function getResults($query, $bind = "", $output = null) {
		if (is_null($query) || empty($query)) return null;	
		
		$this->_do_query($query, $bind);
		if ($this->_result) {
			return $this->_stmt->fetchAll($output ?: PDO::FETCH_OBJ);
		}
		
		return null;
	}
	
	/**
	 * Retrieve one row from the database.
	 *
	 * Executes a SQL query and returns the row from the SQL result.
	 *
	 * @param string|null $query SQL query.
	 * @param array $bind The parameters to bind to the SQL statement parameter
	 * @param string $output Optional. See Apdb::getResult()
	 * @return mixed Database query result in format specified by $output or null on failure
	 */
	public function getRow($query = null, $bind = "", $output = null) {
		if (is_null($query) || empty($query)) return null;	
		
		$this->_do_query($query, $bind);
		if ($this->_result) {
			return $this->_stmt->fetch($output ?: PDO::FETCH_OBJ);
		}
		
		return null;
	}
	
	/**
	 * Retrieve one column from the database.
	 *
	 * Executes a SQL query and returns the column from the SQL result.
	 * If the SQL result contains more than one column, this function returns
	 * the column specified. If $query is null, this function returns the
	 * specified column from the previous SQL result.
	 *
	 * @param string|null $query Optional. SQL query. Defaults to null, use the
	 *	result from the previous query.
	 * @param array $bind The parameters to bind to the SQL statement parameter
	 * @param int $x Optional. Column to return. Indexed from 0.
	 *
	 * @return array|null Query result. Array indexed from 0 by SQL result row number.
	 */
	public function getCol($query = null, $bind = "", $x = 0) {
		if (is_null($query) || empty($query)) return null;	
		
		$rows = $this->getResults($query, $bind, PDO::FETCH_NUM);
		if ($rows) {
			if (!is_int($x)) $x = 0;
			if ($x > count($rows[0])) $x = count($rows[0])-1;
			
			// Extract the column values
			$new_array = array();
			foreach ($rows as $key => $row) {
				$new_array[$key] = $row[$x];
			}
		}
		
		return isset($new_array) ? $new_array : null;
	}
	
	/**
	 * Retrieve one variable from the database.
	 *
	 * @param string|null $query Optional. SQL query. Defaults to null.
	 * @param array $bind The parameters to bind to the SQL statement parameter
	 * 
	 * @return string|null Database query result (as string), or null on failure
	 */
	public function getVar($query = null, $bind = "") {
		if (!$query) return null;
		
		return $this->getRow($query, $bind, PDO::FETCH_COLUMN);
	}
	
	/**
	 * Insert a row into a table
	 *
	 * 	Apdb::insert('table', array('column' => 'foo', 'field' => 'bar'))
	 *
	 * @param string $table table name
	 * @param array $data Data to insert (in column => value pairs).
	 *
	 * @return int|false The number of rows inserted, or false on error.
	 */
	public function insert($table, $data) {
		if (empty($table) || !is_array($data)) {
			return false;
		}
		
		$fields = $placeHolder = array();
		foreach ($data as $field => $value) {
			$data[":$field"] = $value;
			$fields[] = "`$field`";
			$placeHolder[] = ":$field";
			unset($data[$field]);
		}
		
		$fields = implode(',', $fields);
		$placeHolder = implode(',', $placeHolder);
		
		$query = "INSERT INTO `$table` ($fields) VALUES ($placeHolder)";
		return $this->query($query, $data);
	}
	
	/**
	 * Update a row in the table
	 *
	 *	Apdb::update('table',array('column' => 'foo', 'field' => 'bar'), array('ID' => 1))
	 *
	 * @param string $table table name
	 * @param array $data Data to update (in column => value pairs)
	 * @param array $where A named array of WHERE clauses (in column => value pairs).
	 * Multiple clauses will be joined with ANDs.
	 *
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update($table, $data, $where) {
		if (empty($table) || !is_array($data) || !is_array($where)) {
			return false;
		}
		
		$fields = $bind = $conditions = array();
		// proccess fields
		foreach ($data as $field => $value) {
			$fields[] = "`$field`=:new$field";
			$bind[":new$field"] = $value;
		}
		
		// proccess conditions
		foreach ($where as $field => $value) {
			$conditions[] = "`$field`=:$field";
			$bind[":$field"] = $value;
		}
		
		$fields = implode(',', $fields);
		$conditions = implode( ' AND ', $conditions );
		
		$query = "UPDATE `$table` SET $fields WHERE $conditions";
		return $this->query($query, $bind);
	}
	
	/**
	 * Delete a row in the table
	 *
	 * 	Apdb::delete('table', array('ID' => 1))
	 *
	 * @param string $table table name
	 * @param array $where A named array of WHERE clauses (in column => value pairs).
	 * Multiple clauses will be joined with ANDs.
	 *
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function delete($table, $where) {
		if (empty($table) || !is_array($where)) {
			return false;
		}
		
		$conditions = array();
		foreach ($where as $field => $value) {
			$conditions[] = "`$field`=:$field";
			$data[':'.$field] = $value;
			unset($data[$field]);
		}
		
		$conditions = implode( ' AND ', $conditions );
		
		$query = "DELETE FROM `$table` WHERE $conditions";
		return $this->query($query, $data);
	}
	
	/**
	 * Starts the timer, for debugging purposes.
	 * 
	 * @return int
	 */
	public function timer_start() {
		return microtime(true);
	}

	/**
	 * Stops the debugging timer.
	 *
	 * @param int $time_start 开始时间戳
	 * @return float Total time spent on the query, in seconds
	 */
	public function timer_stop($time_start) {
		return (microtime(true) - $time_start);
	}
	
	/**
	 * Return an array of available PDO drivers
	 *
	 * @return array
	 */
	public static function getAvailableDrivers() {
		return PDO::getAvailableDrivers();
	}
	
	/**
	 * Dump an SQL prepared command
	 *
	 */
	public function debugDumpParams() {
		if (is_null($this->_stmt)) return null;
		return $this->_stmt->debugDumpParams();
	}
	
	/**
	 * The database version number.
	 *
	 * @return null|string Null on failure, version number on success.
	 */
	public function dbVersion() {
		$col = $this->getCol("SHOW VARIABLES LIKE 'version'", "", 1);
		if (is_array($col)) return $col[0];
		
		return null;
	}
}